﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.Linq;
using System.Text;

namespace ToursAgencyDAL
{
    public class ToursAgencyDataFuncs
    {

        public static IQueryable<Tour> GetAllTours()
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var allTours = from t in dbContext.Tours
                           select t;
            return allTours;
        }

        public static IQueryable GetTourByIDAsIqueriable(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var tour = (from t in dbContext.Tours
                        where t.ID == id
                        select t);
            return tour;
        }

        public static Tour GetTourByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var tour = (from t in dbContext.Tours
                        where t.ID == id
                        select t).Single();
            return tour;
        }

        public static int GetReservationIDBYRefNumber(string refNumber)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var resID = (from r in dbContext.Reservations
                         where r.ReferenceNumber.Equals(refNumber)
                         select r.ID).Single();
            return resID;
        }

        public static IQueryable GetAllHotelFacilitiesByTourID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var hotelFacs = from th in dbContext.TourHotelFacilities.Include("HotelFacilities")
                            where th.TourID == id
                            select new { Name = th.HotelFacility.Name };
            return hotelFacs;
        }

        public static string[] GetAllDestinationsToArray()
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var destinations = (from t in dbContext.Destinations
                                select t.Name).ToArray();
            return destinations;
        }

        public static IQueryable GetAllDestinations()
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var destinations = (from t in dbContext.Destinations
                                select t);
            return destinations;
        }

        public static string GetDestinationNameByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var destinations = (from t in dbContext.Destinations
                                where t.ID == id
                                select t.Name).Single();
            return destinations;
        }

        public static Destination GetDestinationByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var destination = (from t in dbContext.Destinations
                               where t.ID == id
                               select t).Single();
            return destination;
        }

        public static Transport GetTransportByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var transport = (from t in dbContext.Transports
                             where t.ID == id
                             select t).Single();
            return transport;
        }

        public static Board GetBoardByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var board = (from t in dbContext.Boards
                         where t.ID == id
                         select t).Single();
            return board;
        }

        public static HotelFacility GetHotelFacilityByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var hotelFacility = (from t in dbContext.HotelFacilities
                                 where t.ID == id
                                 select t).Single();
            return hotelFacility;
        }

        public static string GetTransportNameByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var destinations = (from t in dbContext.Transports
                                where t.ID == id
                                select t.Name).Single();
            return destinations;
        }

        public static string GetBoardNameByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var boardName = (from t in dbContext.Boards
                             where t.ID == id
                             select t.Name).Single();
            return boardName;
        }

        public static int GetDestinationIDByName(string name)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var destinations = (from t in dbContext.Destinations
                                where t.Name == name
                                select t.ID).Single();
            return destinations;
        }

        public static IQueryable GetAllTransports()
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var transports = (from t in dbContext.Transports
                              select t);
            return transports;
        }

        public static IQueryable GetAllBoards()
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var boards = (from t in dbContext.Boards
                          select t);
            return boards;
        }

        public static IQueryable GetAllFacilities()
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var hotelFacs = (from t in dbContext.HotelFacilities
                             select t);
            return hotelFacs;
        }

        public static void CreateTourHotelFacility(TourHotelFacility th)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.TourHotelFacilities.AddObject(th);
                dbContext.SaveChanges();
            }
        }
        #region main

        public static void CreateTour(Tour t)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.Tours.AddObject(t);
                dbContext.SaveChanges();
            }
        }

        public static void EditTour(Tour t)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                try
                {
                    dbContext.Tours.Attach(t);
                    dbContext.ObjectStateManager.GetObjectStateEntry(t).SetModified();
                    dbContext.Refresh(RefreshMode.ClientWins, t);
                    dbContext.SaveChanges();
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The tour was not found. Check if the id is correct");
                }
            }
        }

        public static void DeleteTour(Tour t)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                int usedEventsCount = (from r in dbContext.Reservations
                                       where r.TourID == t.ID
                                       select r).Count();

                if (usedEventsCount == 0)
                {
                    dbContext.AttachTo("Tours", t);
                    dbContext.Tours.DeleteObject(t);
                    try
                    {
                        dbContext.SaveChanges();
                    }
                    catch (OptimisticConcurrencyException)
                    {
                        Console.WriteLine("There was no tour with that id!");
                    }
                    catch (InvalidOperationException)
                    {
                        Console.WriteLine("The tour was not found. Check if the id is correct");
                    }
                }
            }
        }


        public static void CreateHotelFacility(HotelFacility h)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.HotelFacilities.AddObject(h);
                dbContext.SaveChanges();
            }
        }

        public static void EditHotelFacility(HotelFacility h)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                try
                {
                    dbContext.HotelFacilities.Attach(h);
                    dbContext.ObjectStateManager.GetObjectStateEntry(h).SetModified();
                    dbContext.Refresh(RefreshMode.ClientWins, h);
                    dbContext.SaveChanges();
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The hotel facility was not found. Check if the id is correct");
                }
            }
        }

        public static void DeleteHotelFacility(HotelFacility h)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.AttachTo("HotelFacilities", h);
                dbContext.HotelFacilities.DeleteObject(h);
                try
                {
                    dbContext.SaveChanges();
                }
                catch (OptimisticConcurrencyException)
                {
                    Console.WriteLine("There was no hotel facility with that id!");
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The hotel facility was not found. Check if the id is correct");
                }
            }
        }

        public static IQueryable FindTour(string destination, decimal? startPrice, decimal? endPrice,
            DateTime? startDate, DateTime? endDate, int? refNumber)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            var allTours = from t in dbContext.Tours
                           select t;

            if (destination != null)
            {
                int destID = GetDestinationIDByName(destination);
                allTours = from t in allTours
                           where t.DestinationID == destID
                           select t;
            }

            if (startPrice != null)
            {
                allTours = from t in allTours
                           where t.Price > startPrice
                           select t;
            }

            if (endPrice != null)
            {
                allTours = from t in allTours
                           where t.Price < endPrice
                           select t;
            }

            if (startDate != null)
            {
                allTours = from t in allTours
                           where t.StartDate > startDate
                           select t;
            }

            if (endDate != null)
            {
                allTours = from t in allTours
                           where t.EndDate < endDate
                           select t;
            }

            if (refNumber != null)
            {
                allTours = from t in allTours
                           where t.ID == refNumber
                           select t;
            }

            return allTours;
        }

        public static void CreateTransport(Transport t)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.Transports.AddObject(t);
                dbContext.SaveChanges();
            }
        }

        public static void EditTransport(Transport t)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                try
                {
                    dbContext.Transports.Attach(t);
                    dbContext.ObjectStateManager.GetObjectStateEntry(t).SetModified();
                    dbContext.Refresh(RefreshMode.ClientWins, t);
                    dbContext.SaveChanges();
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The transport was not found. Check if the id is correct");
                }
            }
        }

        public static void DeleteTransport(Transport t)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.AttachTo("Transports", t);
                dbContext.Transports.DeleteObject(t);
                try
                {
                    dbContext.SaveChanges();
                }
                catch (OptimisticConcurrencyException)
                {
                    Console.WriteLine("There was no transport with that id!");
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The transport was not found. Check if the id is correct");
                }
            }
        }

        public static void CreateBoard(Board b)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.Boards.AddObject(b);
                dbContext.SaveChanges();
            }
        }

        public static void EditBoard(Board b)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                try
                {
                    dbContext.Boards.Attach(b);
                    dbContext.ObjectStateManager.GetObjectStateEntry(b).SetModified();
                    dbContext.Refresh(RefreshMode.ClientWins, b);
                    dbContext.SaveChanges();
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The board was not found. Check if the id is correct");
                }
            }
        }

        public static void DeleteBoard(Board b)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.AttachTo("Boards", b);
                dbContext.Boards.DeleteObject(b);
                try
                {
                    dbContext.SaveChanges();
                }
                catch (OptimisticConcurrencyException)
                {
                    Console.WriteLine("There was no board with that id!");
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The board was not found. Check if the id is correct");
                }
            }
        }

        public static void CreateDestination(Destination d)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.Destinations.AddObject(d);
                dbContext.SaveChanges();
            }
        }

        public static void EditDestination(Destination d)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                try
                {
                    dbContext.Destinations.Attach(d);
                    dbContext.ObjectStateManager.GetObjectStateEntry(d).SetModified();
                    dbContext.Refresh(RefreshMode.ClientWins, d);
                    dbContext.SaveChanges();
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The destination was not found. Check if the id is correct");
                }
            }
        }

        public static void DeleteDestination(Destination d)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.AttachTo("Destinations", d);
                dbContext.Destinations.DeleteObject(d);
                try
                {
                    dbContext.SaveChanges();
                }
                catch (OptimisticConcurrencyException)
                {
                    Console.WriteLine("There was no destination with that id!");
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The destination was not found. Check if the id is correct");
                }
            }
        }

        public static void CreateReservation(Reservation r)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.Reservations.AddObject(r);
                dbContext.SaveChanges();
            }
        }

        public static void EditReservation(Reservation r)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                try
                {
                    dbContext.Reservations.Attach(r);
                    dbContext.ObjectStateManager.GetObjectStateEntry(r).SetModified();
                    dbContext.Refresh(RefreshMode.ClientWins, r);
                    dbContext.SaveChanges();
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The reservation was not found. Check if the id is correct");
                }
            }
        }

        public static void DeleteReservation(Reservation r)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.AttachTo("Reservations", r);
                dbContext.Reservations.DeleteObject(r);
                try
                {
                    dbContext.SaveChanges();
                }
                catch (OptimisticConcurrencyException)
                {
                    Console.WriteLine("There was no reservation with that id!");
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The reservation was not found. Check if the id is correct");
                }
            }
        }

        public static void CreatePerson(Person p)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.People.AddObject(p);
                dbContext.SaveChanges();
            }
        }

        public static void EditPerson(Person p)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                try
                {
                    dbContext.People.Attach(p);
                    dbContext.ObjectStateManager.GetObjectStateEntry(p).SetModified();
                    dbContext.Refresh(RefreshMode.ClientWins, p);
                    dbContext.SaveChanges();
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The person was not found. Check if the id is correct");
                }
            }
        }

        public static void DeletePerson(Person p)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            using (dbContext)
            {
                dbContext.AttachTo("People", p);
                dbContext.People.DeleteObject(p);
                try
                {
                    dbContext.SaveChanges();
                }
                catch (OptimisticConcurrencyException)
                {
                    Console.WriteLine("There was no person with that id!");
                }
                catch (InvalidOperationException)
                {
                    Console.WriteLine("The person was not found. Check if the id is correct");
                }
            }
        }

        public static Reservation GetReservationByID(int id)
        {
            ToursAgencyEntities dbContext = new ToursAgencyEntities();
            Reservation res = (from r in dbContext.Reservations
                               where r.ID == id
                               select r).Single();
            return res;
        }

        public static void MakePayment(decimal payment, int reservationID)
        {
            Reservation currentReservation = GetReservationByID(reservationID);
            currentReservation.PaidPrice += payment;
            if (currentReservation.PaidPrice == currentReservation.TotalPrice)
            {
                currentReservation.Status = "Confirmed";
            }
            else
            {
                currentReservation.Status = "Partially Paid";
            }
            EditReservation(currentReservation);
        }

        #endregion
    }
}
